#!/bin/bash

# 第一个数据库连接参数
DB_USER="GanLan"
DB_PASS="hwT6LL68CD62fRHm"
DB_HOST="107.167.34.217"
DB_NAME="article_data"

# 第二个数据库连接参数
DB2_USER="sqlim7jTA"
DB2_PASS="whsYcF"
DB2_HOST="107.167.34.217"
DB2_NAME="sqlim7jta"

# 获取当天日期（假设数据库的时间格式是 'YYYY-MM-DD'）
TODAY_DATE=$(date +%Y-%m-%d)

# 执行查询：检查新增数据（create_time > 当天时间）
NEW_DATA_COUNT=$(mysql -h $DB_HOST -u $DB_USER -p$DB_PASS $DB_NAME -se "SELECT COUNT(*) FROM \`article\` WHERE create_time > '$TODAY_DATE'")

# 执行查询：检查更新数据（update_time > 当天时间）
UPDATED_DATA_COUNT=$(mysql -h $DB_HOST -u $DB_USER -p$DB_PASS $DB_NAME -se "SELECT COUNT(*) FROM \`article\` WHERE update_time > '$TODAY_DATE'")

# 如果查询结果为空，设置为0
NEW_DATA_COUNT=${NEW_DATA_COUNT:-0}
UPDATED_DATA_COUNT=${UPDATED_DATA_COUNT:-0}

# 判断是否有新增数据
if [ "$NEW_DATA_COUNT" -gt 0 ]; then
  echo "文章库有新增数据！"
  echo "新增数据条数: $NEW_DATA_COUNT"

  # 列出新增数据的所有内容
  NEW_ARTICLES=$(mysql -h $DB_HOST -u $DB_USER -p$DB_PASS $DB_NAME -se "SELECT * FROM \`article\` WHERE create_time > '$TODAY_DATE'")

  # 将新增文章插入到第二个数据库
  while IFS=$'\t' read -r atl_id ATL_TITLE ATL_CONTENT_STR ATL_CONTENT ATL_IMG ATL_IMG_ORIGINAL ATL_TYPE CREATE_TIME UPDATE_TIME POST_AUTHOR POST_NAME; do
    # 提取字段，并防止单引号导致SQL错误
    ATL_TITLE=$(echo "$ATL_TITLE" | sed "s/'/''/g")
    ATL_CONTENT=$(echo "$ATL_CONTENT" | sed "s/'/''/g")
    POST_AUTHOR=$(echo "$POST_AUTHOR" | sed "s/'/''/g")
    POST_NAME=$(echo "$POST_NAME" | sed "s/'/''/g")
    CREATE_TIME=$(echo "$CREATE_TIME" | sed "s/'/''/g")
    UPDATE_TIME=$(echo "$UPDATE_TIME" | sed "s/'/''/g")

    # 插入数据到第二个数据库
    mysql -h $DB2_HOST -u $DB2_USER -p$DB2_PASS $DB2_NAME -e "
    INSERT INTO wp_posts (post_title, post_content, post_author, post_name, post_date, post_date_gmt, post_modified, post_modified_gmt, post_status, to_ping, pinged, post_content_filtered, post_excerpt, post_type) 
    VALUES ('$ATL_TITLE', '$ATL_CONTENT', '$POST_AUTHOR', '$POST_NAME', '$CREATE_TIME', '$CREATE_TIME', '$UPDATE_TIME', '$UPDATE_TIME', 'publish', '', '', '', '', 'post');
    "


    echo "文章 '$ATL_TITLE' 已成功插入第二个数据库。"
  done <<< "$NEW_ARTICLES"
else
  echo "文章库没有新增数据。"
fi

# 判断是否有更新数据
if [ "$UPDATED_DATA_COUNT" -gt 0 ]; then
  echo "文章库有更新数据！"
  echo "更新数据条数: $UPDATED_DATA_COUNT"

  # 列出更新数据的所有内容
  UPDATED_ARTICLES=$(mysql -h $DB_HOST -u $DB_USER -p$DB_PASS $DB_NAME -se "SELECT * FROM \`article\` WHERE update_time > '$TODAY_DATE'")

  # 将更新文章数据更新到第二个数据库
  while IFS=$'\t' read -r atl_id ATL_TITLE ATL_CONTENT_STR ATL_CONTENT ATL_IMG ATL_IMG_ORIGINAL ATL_TYPE CREATE_TIME UPDATE_TIME POST_AUTHOR POST_NAME; do
    # 提取字段，并防止单引号导致SQL错误
    ATL_TITLE=$(echo "$ATL_TITLE" | sed "s/'/''/g")
    ATL_CONTENT=$(echo "$ATL_CONTENT" | sed "s/'/''/g")
    POST_AUTHOR=$(echo "$POST_AUTHOR" | sed "s/'/''/g")
    POST_NAME=$(echo "$POST_NAME" | sed "s/'/''/g")
    CREATE_TIME=$(echo "$CREATE_TIME" | sed "s/'/''/g")
    UPDATE_TIME=$(echo "$UPDATE_TIME" | sed "s/'/''/g")

    # 更新数据到第二个数据库
    mysql -h $DB2_HOST -u $DB2_USER -p$DB2_PASS $DB2_NAME -e "
    UPDATE wp_posts 
    SET post_content = '$ATL_CONTENT', 
        post_author = '$POST_AUTHOR', 
        post_name = '$POST_NAME', 
        post_modified = '$UPDATE_TIME', 
        post_modified_gmt = '$UPDATE_TIME' 
    WHERE post_title = '$ATL_TITLE';
    "

    echo "文章 '$ATL_TITLE' 已成功更新在第二个数据库。"
  done <<< "$UPDATED_ARTICLES"
else
  echo "文章库没有更新数据。"
fi
